package com.lovo.roomProject.student.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClasszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;
import com.lovo.roomProject.student.dao.StudentDAO;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl extends BaseDAO implements StudentDAO{

    /**
     * 查询
     * @return
     */
    @Override
    public List listStudent() {
        this.getConnection();
        String sql="select s.*,c.*,r.* from t_student s left join t_class c on c.c_id=s.s_c_id" +
                " left join t_room r on r.r_id=s.s_r_id";
        List list =new ArrayList();
        try {
            statement=con.prepareStatement(sql);
            rs=statement.executeQuery();
            while (rs.next()){
                StudentDO studentDO=fillStudent();
                list.add(studentDO);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close(rs,statement,con);
        }
        return list;
    }

    @Override
    public StudentDO fillStudent() throws SQLException{
        StudentDO studentDO=new StudentDO();
        studentDO.setId(rs.getInt("s_id"));
        studentDO.setName(rs.getString("s_name"));
        studentDO.setGender(rs.getString("s_gender"));
        studentDO.setPhone(rs.getString("s_phone"));
        studentDO.setDate(LocalDate.parse(rs.getString("s_date")));
        studentDO.setHead(rs.getString("s_head"));

        ClasszDO classzDO=new ClasszDO();
        classzDO.setId(rs.getInt("c_id"));
        classzDO.setName(rs.getString("c_name"));
        studentDO.setClassz(classzDO);

        RoomDO roomDO=new RoomDO();
        roomDO.setId(rs.getInt("r_id"));
        roomDO.setAddr(rs.getString("r_addr"));
        studentDO.setRoom(roomDO);
        return studentDO;
    }

    @Override
    public StudentDO getStudentID(int id) {
        this.getConnection();
        String sql="select s.*,c.*,r.* from t_student s left join t_class c on c.c_id=s.s_c_id" +
                " left join t_room r on r.r_id=s.s_r_id where s.s_id=?";
        StudentDO studentDO=new StudentDO();
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            rs=statement.executeQuery();
            if (rs.next()){
                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setGender(rs.getString("s_gender"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setDate(LocalDate.parse(rs.getString("s_date")));
                studentDO.setHead(rs.getString("s_head"));

                ClasszDO classzDO=new ClasszDO();
                classzDO.setId(rs.getInt("c_id"));
                classzDO.setName(rs.getString("c_name"));
                studentDO.setClassz(classzDO);

                RoomDO roomDO=new RoomDO();
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                studentDO.setRoom(roomDO);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close(rs,statement,con);
        }
        return studentDO;
    }

    @Override
    public void insert(StudentDO studentDO) {
        this.getConnection();
        String sql="INSERT INTO t_student (s_name,s_gender,s_phone,s_date,s_head,s_c_id,s_r_id) VALUES (?,?,?,?,?,?,?)";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,studentDO.getName());
            statement.setObject(2,studentDO.getGender());
            statement.setObject(3,studentDO.getPhone());
            statement.setObject(4,studentDO.getDate());
            statement.setObject(5,studentDO.getHead());
            statement.setObject(6,studentDO.getClassz() !=null ? studentDO.getClassz().getId() : null);
            statement.setObject(7,studentDO.getRoom() !=null ? studentDO.getRoom().getId() : null);
            statement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            close();
        }
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql="delete from t_student where s_id=?";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            statement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            close();
        }
    }

    @Override
    public void update(StudentDO studentDO) {
        this.getConnection();
        String sql="update t_student set s_r_id=? where s_id=?";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,studentDO.getRoom().getId());
            statement.setObject(2,studentDO.getId());
            statement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            close();
        }
    }

    @Override
    public CutPageBean listStudentPage(int current,String studentName,String classzName,String roomName) {
        this.getConnection();
        String selectSql="select s.*,c.*,r.* ";
        String countSql="select count(*) cnt ";
        String fromSql="from t_student s left join t_class c " +
                " on c.c_id=s.s_c_id left join t_room r on r.r_id=s.s_r_id ";
        String whereSql=" where 1=1";
        String limitSql=" limit ?,? ";
        if (studentName!=null && !studentName.equals("")){
            whereSql += " and s.s_name like '%"+studentName+"%'";
        }
        if (classzName!=null && !classzName.equals("")){
            whereSql += " and c.c_name like '%"+classzName+"%'";
        }
        if (roomName!=null && !roomName.equals("")){
            whereSql += " and r.r_addr like '%"+roomName+"%'";
        }
        CutPageBean cutPageBean=new CutPageBean<>();
        List list=new ArrayList();
        try {
            statement=con.prepareStatement(selectSql+fromSql+whereSql+limitSql);
            statement.setObject(1,(current-1)*CutPageBean.PAGESIZE);
            statement.setObject(2,CutPageBean.PAGESIZE);
            rs=statement.executeQuery();
            while (rs.next()){
                StudentDO studentDO=fillStudent();
                list.add(studentDO);
            }
            cutPageBean.setList(list);
            // 计算总数的SQL
            statement=con.prepareStatement(countSql+fromSql+whereSql);
            rs=statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            close(rs,statement,con);
        }
        return cutPageBean;
    }

}
